package com.sinosoft.product;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.classic.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.sinosoft.base.GenericDaoHibernate;

@Repository("productSearchDao")
public class ProductSearchDaoHibernate extends GenericDaoHibernate<ZywxImage1, Long> implements ProductSearchDao{
	
	private String products;
	
	public ProductSearchDaoHibernate() {
		super(ZywxImage1.class);
	}
	
	public String doProductSearch(final String querySql){
		this.getSessionFactory().getCurrentSession().doWork(new Work(){
			ResultSet rs = null;
			Statement stmt = null;
			public void execute(Connection connection) throws SQLException 
	        { 
	            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//	            rs = stmt.executeQuery("select * from zywx_image_1 t where rownum between 1 and 10");
	            System.out.println(querySql);
	            rs = stmt.executeQuery(querySql);
	            StringBuffer sb = new StringBuffer("[");
	    		while(rs!=null && rs.next()){
	    			sb.append("{");
	    			String thumImgUrl = "http://localhost:8080/zywx3/images/thum.jpg";
	    			String productId = rs.getString("PRODUCTID");
	    			String satellite = rs.getString("SATELLITEID");
	    			String sensor = rs.getString("SENSORID");
	    			String endTime = rs.getString("ENDTIME");
	    			String productLevel = rs.getString("PRODUCTLEVEL");
	    			String path = rs.getString("SCENEPATH");
	    			String row = rs.getString("SCENEROW");
	    			String satPath = rs.getString("SATPATH");
	    			String satRow = rs.getString("SATROW");
	    			String centerLong = rs.getString("CENTERLONGITUDE");
	    			String centerLat = rs.getString("CENTERLATITUDE");
	    			String sceneId = rs.getString("SCENEID");
	    			System.out.println(sceneId);
	    			
	    			String tlLat = rs.getString("TOPLEFTLATITUDE");
	    			String tlLong = rs.getString("TOPLEFTLONGITUDE");
	    			String trLat = rs.getString("TOPRIGHTLATITUDE");
	    			String trLong = rs.getString("TOPRIGHTLONGITUDE");
	    			String brLat = rs.getString("BOTTOMRIGHTLATITUDE");
	    			String brLong = rs.getString("BOTTOMRIGHTLONGITUDE");
	    			String blLat = rs.getString("BOTTOMLEFTLATITUDE");
	    			String blLong = rs.getString("BOTTOMLEFTLONGITUDE");
	    			
	    			sb.append("\"thumImgUrl\":\"" + thumImgUrl + "\",");
	    			sb.append("\"productId\":\"" + productId + "\",");
	    			sb.append("\"satellite\":\"" + satellite + "\",");
	    			sb.append("\"sensor\":\"" + sensor + "\",");
	    			sb.append("\"endTime\":\"" + endTime + "\",");
	    			sb.append("\"productLevel\":\"" + productLevel + "\",");
	    			sb.append("\"path\":\"" + path + "\",");
	    			sb.append("\"row\":\"" + row + "\",");
	    			sb.append("\"satPath\":\"" + satPath + "\",");
	    			sb.append("\"satRow\":\"" + satRow + "\",");
	    			sb.append("\"centerLong\":\"" + centerLong + "\",");
	    			sb.append("\"centerLat\":\"" + centerLat + "\",");
	    			sb.append("\"tlLat\":\"" + tlLat + "\",");
	    			sb.append("\"tlLong\":\"" + tlLong + "\",");
	    			sb.append("\"trLat\":\"" + trLat + "\",");
	    			sb.append("\"trLong\":\"" + trLong + "\",");
	    			sb.append("\"brLat\":\"" + brLat + "\",");
	    			sb.append("\"brLong\":\"" + brLong + "\",");
	    			sb.append("\"blLat\":\"" + blLat + "\",");
	    			sb.append("\"sceneId\":\"" + sceneId + "\",");
	    			sb.append("\"blLong\":\"" + blLong + "\"");
	    			if(!rs.isLast()){
	    				sb.append("},");
	    			}
	    			else{
	    				sb.append("}");
	    			}
	            }
	    		stmt.close();
	    		rs.close();
//	    		connection.close();
	    		sb.append("]");
	    		products = sb.toString();
	        }
		});
		return products;
	}
	
	public String doSearchLevel0(final String querySql){
		this.getSessionFactory().getCurrentSession().doWork(new Work(){
			ResultSet rs = null;
			Statement stmt = null;
			public void execute(Connection connection) throws SQLException 
	        { 
	            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	            System.out.println(querySql);
	            rs = stmt.executeQuery(querySql);
	            StringBuffer sb = new StringBuffer("[");
	    		while(rs!=null && rs.next()){
	    			sb.append("{");
	    			String thumImgUrl = "http://localhost:8080/zywx3/images/thum.jpg";
	    			//level0只有原始数据ID
	    			String productId = rs.getString("RAWDATAID");
	    			String satellite = rs.getString("SATELLITEID");
	    			String sensor = rs.getString("SENSORID");
	    			String endTime = rs.getString("IMAGINGSTOPTIME");
	    			//String productLevel = rs.getString("PRODUCTLEVEL");
	    			String path = rs.getString("SCENEPATH");
	    			String row = rs.getString("SCENEROW");
	    			String satPath = rs.getString("SATPATH");
	    			String satRow = rs.getString("SATROW");
	    			String centerLong = rs.getString("SCENESERTERLONG");
	    			String centerLat = rs.getString("SCENECERTERLAT");
	    			String sceneId = rs.getString("SCENEID");
	    			System.out.println(sceneId);
	    			
	    			String tlLat = rs.getString("UPPERLEFTLAT");
	    			String tlLong = rs.getString("UPPERLEFTLONG");
	    			String trLat = rs.getString("UPPERRIGHLAT");
	    			String trLong = rs.getString("UPPERRIGHTLONG");
	    			String brLat = rs.getString("LOWERRIGHTLAT");
	    			String brLong = rs.getString("LOWERRIGHTLONG");
	    			String blLat = rs.getString("LOWERLEFTLAT");
	    			String blLong = rs.getString("LOWERLEFTLONG");
	    			
	    			sb.append("\"thumImgUrl\":\"" + thumImgUrl + "\",");
	    			sb.append("\"productId\":\"" + productId + "\",");
	    			sb.append("\"satellite\":\"" + satellite + "\",");
	    			sb.append("\"sensor\":\"" + sensor + "\",");
	    			sb.append("\"endTime\":\"" + endTime + "\",");
//	    			sb.append("\"productLevel\":\"" + productLevel + "\",");
	    			sb.append("\"path\":\"" + path + "\",");
	    			sb.append("\"row\":\"" + row + "\",");
	    			sb.append("\"satPath\":\"" + satPath + "\",");
	    			sb.append("\"satRow\":\"" + satRow + "\",");
	    			sb.append("\"centerLong\":\"" + centerLong + "\",");
	    			sb.append("\"centerLat\":\"" + centerLat + "\",");
	    			sb.append("\"tlLat\":\"" + tlLat + "\",");
	    			sb.append("\"tlLong\":\"" + tlLong + "\",");
	    			sb.append("\"trLat\":\"" + trLat + "\",");
	    			sb.append("\"trLong\":\"" + trLong + "\",");
	    			sb.append("\"brLat\":\"" + brLat + "\",");
	    			sb.append("\"brLong\":\"" + brLong + "\",");
	    			sb.append("\"blLat\":\"" + blLat + "\",");
	    			sb.append("\"sceneId\":\"" + sceneId + "\",");
	    			sb.append("\"blLong\":\"" + blLong + "\"");
	    			if(!rs.isLast()){
	    				sb.append("},");
	    			}
	    			else{
	    				sb.append("}");
	    			}
	            }
	    		stmt.close();
	    		rs.close();
//	    		connection.close();
	    		sb.append("]");
	    		products = sb.toString();
	        }
		});
		return products;
	}
	
	public String doSpecialSearch(final String querySql){
		this.getSessionFactory().getCurrentSession().doWork(new Work(){
			ResultSet rs = null;
			Statement stmt = null;
			public void execute(Connection connection) throws SQLException 
	        { 
	            stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
//	            rs = stmt.executeQuery("select * from zywx_image_1 t where rownum between 1 and 10");
	            System.out.println(querySql);
	            rs = stmt.executeQuery(querySql);
	            StringBuffer sb = new StringBuffer("[");
	    		while(rs!=null && rs.next()){
	    			sb.append("{");
	    			String thumImgUrl = "http://localhost:8080/zywx3/images/thum.jpg";
	    			String productId = rs.getString("PRODUCTID");
	    			String satellite = rs.getString("SATELLITEID");
	    			String sensor = rs.getString("SENSORID");
	    			String endTime = rs.getString("ENDTIME");
	    			String productLevel = rs.getString("PRODUCTLEVEL");
	    			String path = rs.getString("SCENEPATH");
	    			String row = rs.getString("SCENEROW");
	    			String satPath = rs.getString("SATPATH");
	    			String satRow = rs.getString("SATROW");
	    			String centerLong = rs.getString("CENTERLONGITUDE");
	    			String centerLat = rs.getString("CENTERLATITUDE");
	    			
	    			String tlLat = rs.getString("TOPLEFTLATITUDE");
	    			String tlLong = rs.getString("TOPLEFTLONGITUDE");
	    			String trLat = rs.getString("TOPRIGHTLATITUDE");
	    			String trLong = rs.getString("TOPRIGHTLONGITUDE");
	    			String brLat = rs.getString("BOTTOMRIGHTLATITUDE");
	    			String brLong = rs.getString("BOTTOMRIGHTLONGITUDE");
	    			String blLat = rs.getString("BOTTOMLEFTLATITUDE");
	    			String blLong = rs.getString("BOTTOMLEFTLONGITUDE");
	    			
	    			sb.append("\"thumImgUrl\":\"" + thumImgUrl + "\",");
	    			sb.append("\"productId\":\"" + productId + "\",");
	    			sb.append("\"satellite\":\"" + satellite + "\",");
	    			sb.append("\"sensor\":\"" + sensor + "\",");
	    			sb.append("\"endTime\":\"" + endTime + "\",");
	    			sb.append("\"productLevel\":\"" + productLevel + "\",");
	    			sb.append("\"path\":\"" + path + "\",");
	    			sb.append("\"row\":\"" + row + "\",");
	    			sb.append("\"satPath\":\"" + satPath + "\",");
	    			sb.append("\"satRow\":\"" + satRow + "\",");
	    			sb.append("\"centerLong\":\"" + centerLong + "\",");
	    			sb.append("\"centerLat\":\"" + centerLat + "\",");
	    			sb.append("\"tlLat\":\"" + tlLat + "\",");
	    			sb.append("\"tlLong\":\"" + tlLong + "\",");
	    			sb.append("\"trLat\":\"" + trLat + "\",");
	    			sb.append("\"trLong\":\"" + trLong + "\",");
	    			sb.append("\"brLat\":\"" + brLat + "\",");
	    			sb.append("\"brLong\":\"" + brLong + "\",");
	    			sb.append("\"blLat\":\"" + blLat + "\",");
	    			sb.append("\"blLong\":\"" + blLong + "\"");
	    			if(!rs.isLast()){
	    				sb.append("},");
	    			}
	    			else{
	    				sb.append("}");
	    			}
	            }
	    		stmt.close();
	    		rs.close();
//	    		connection.close();
	    		sb.append("]");
	    		products = sb.toString();
	        }
		});
		return products;
	}
	
	
	private String genHql(final String productLevel, final String productId){
		StringBuffer sb = new StringBuffer("from ZywxImage1a where productid=");
		sb.append(productId);
		return sb.toString();
	}
	
	private String genHql4GetProducts(final String level, final String keys){
		String[] ids = keys.split(","); 
		StringBuffer sb = new StringBuffer("from ");
		sb.append(level).append(" where ");
		for(int i=0;i<ids.length;i++){
			sb.append("METADATAID = '");
			if(i == (ids.length - 1)){
				sb.append(ids[i]).append("'");
			}
			else{
				sb.append(ids[i]).append("' or ");
			}
		}
		return sb.toString();
	}
	
	public Object getMetaDataById(final String levelAndId){
		Object record = null;
		
		String[] strs = levelAndId.split("_");
		String productLevel = strs[0];
		String productId = strs[1];
		String hql = genHql(productLevel, productId);
		List<Object> products = this.getHibernateTemplate().find(hql);
		if(!products.isEmpty()){
			record = products.get(0);
		}
		return record;
	}
	
	public List<Object> getProducts(final String level, final String keys){
		String hql = this.genHql4GetProducts(level, keys);
//		String hql = "from ZywxImageHighSdom where productid = 4951 or productid = 41951";
		List<Object> productList = this.getHibernateTemplate().find(hql);
		System.out.println(productList.size());
		return productList;
	}
	
	/**
	 * 查询元数据
	 */
	public Object getZywxImageModel(String sql)
	{
		List<Object> objlist = getHibernateTemplate().find(sql);
		if(objlist.size()>0)
			return objlist.get(0);
		else
			return null;
	}
	
	/**
	 * 元数据列表
	 * @param hql
	 * @return
	 */
	public List<Object> getZywxImageList(String hql,int maxsize)
	{
		Session session = this.getHibernateTemplate().getSessionFactory().openSession();
		Query query = session.createQuery(hql);
		query.setFirstResult(0);
		query.setMaxResults(maxsize);
		List<Object> list = query.list();
//		List<Object> list = this.getHibernateTemplate().find(hql);
		return list;
	}
	
	/**
	 * 元数据过滤
	 */
	public List ProductFilterDao(String sql)
	{
		Session session = this.getHibernateTemplate().getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery(sql);
		List list = query.list();
		session.close();
		return list;
	}
}
